Re: Using the IN predicate in an UPDATE...

At 15:06 +0200 on 14/10/98, Thomas Good wrote:


> This query takes 20 years (poetic licence invoked ;-).
> Do you have a smarter way to accomplish this end:
>
> UPDATE table1 SET id = 2
> WHERE rec_num IN
>  ( SELECT rec_num
>    FROM table1
>    WHERE id = 1
>   );
>
> This is an attempt to cleanup some user error...I have an old
> foxpro db that uses a char str as an index and this index has no
> check constraints (like, making the index unique...ouch.)
> The new pg db is performing nicely, now that it's live...and
> housing 12 years worth of data.  But I am saddled with quite a bit
> of housekeeping - correcting anomalies that were part and parcel
> of the original (foxpro) design.  Or absence thereof.

I didn't know I became an SQL guru... I don't even have the proper version
of Postgres for subqueries...

It all depends on what you have in mind. First, is this an operation you
are likely to perform frequently, or is this a one-time fix you want to run
on your system?

Which fields are indexed? If the id field is indexed, it seems to me the
above query should not be *that* lengthy. In any case, an EXISTS query is
supposed to be more efficient. Especially if the only index is on rec_num.
Try this:

UPDATE table1 SET id = 2
WHERE EXISTS (
    SELECT *
    FROM table1 t1
    WHERE t1.rec_num = table1.rec_num
      AND id = 1
);

I hope the above table aliasing scopes rec_num correctly... I have no way
of trying it myself, because, as I said, I don't have 6.3 as yet.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma